<?php

//Yii::import('llamadas.models._base.BaseReporte');
//class LlamadaReporte extends BaseReporte {
class MailReporte extends CFormModel {

//Atributos
    public $fechas;

//   public static $tiposLlamadas = array(
//       'Recibida','Atendida','Abandonada'
//   );

    public static function model($className = __CLASS__) {
        return parent::model($className);
    }

    public function rules() {
        return array(
            array('fechas', 'required'), //pendiente
        );
    }

    /**
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels() {
        return array(
            'fechas' => Yii::t('app', 'Filtro'),
        );
    }

    public function generatePieReport($inicio, $fin) {
        $inicio = DateTime::createFromFormat('d/m/Y', $inicio);
        $fin = DateTime::createFromFormat('d/m/Y', $fin)->modify('+1 day');
        $report = array();
        $report['title']['text'] = 'Asuntos';
        $report['credits']['enabled'] = false;
        $report['chart']['height'] = '280';
        $report['plotOptions'] = array(
            'pie' => array(
                'allowPointSelect' => true,
                'cursor' => 'pointer',
                'dataLabels' => array(
                    'enabled' => false,
                    'format' => '<b>{point.name}</b>: {point.percentage:.1f} %'
                ),
                'showInLegend' => true,
                'tooltip' => array(
                    'pointFormat' => '{series.name}: <b>{point.percentage:.1f}%</b>'
                ),
            )
        );
        $report['series'] = array();
        $consulta = Yii::app()->db->createCommand()
                ->select('ma.nombre, count(m.id) as count')
                ->from('mail m')
                ->leftJoin('mail_asunto ma', 'ma.id = m.asunto_id')
                ->where('(ma.estado=:estado or ma.estado is null) and m.estado=:estadom and m.fecha_envio between :inicio and :fin', array(':estado' => 'ACTIVO', ':estadom' => 'ENVIADO', ':inicio' => $inicio->format('Y-m-d'), ':fin' => $fin->format('Y-m-d')))
                ->group('ma.id')
                ->order('ma.nombre');

        $options = $consulta->queryAll();


        $series = array(
            'type' => 'pie',
            'name' => 'Cantidad',
            'data' => array()
        );
        foreach ($options as $option) {
            $series['data'][] = array($option['nombre'] ? Util::Truncate($option['nombre'], 21) : 'Otros', (int) $option['count']);
        }
        $report['series'][] = $series;
        return $report;
    }

    public function generateColumnReport($inicio, $fin) {
        $inicio = DateTime::createFromFormat('d/m/Y', $inicio)->setTime(0, 0, 0);
        $fin = DateTime::createFromFormat('d/m/Y', $fin)->setTime(0, 0, 0);
        $interval = DateInterval::createFromDateString('1 day');
        $period = new DatePeriod($inicio, $interval, $fin); //frecuencia del intervalo a calcular
        $dias = 0;
        $report = array();
        $report['title']['text'] = 'Mails';
        $report['credits']['enabled'] = false;
        $report['chart']['height'] = '320';
        $report['subtitle']['text'] = ' Desde: ' . $inicio->format('d-m-Y') . '  Hasta: ' . $fin->format('d-m-Y');
        $report['xAxis']['labels'] = array("rotation" => -45);
        $report['yAxis']['min'] = 0;
        $report['yAxis']['title']['text'] = "Número de Mails";
        $report['yAxis']['allowDecimals'] = false;
        $report['xAxis']['categories'] = array();
        $report['series'] = array();
        $motivos = MailAsunto::model()->activos()->findall();
        $sinmotivo = new MailAsunto();
        $sinmotivo->id = null;
        $sinmotivo->nombre = 'Otros';
        array_push($motivos, $sinmotivo);
        foreach ($period as $date) {
            $dias++;
        }
        switch (true) {
//horas
            case ($dias == 0):
                $interval = DateInterval::createFromDateString('1 hour');
                $fin->add(new DateInterval('PT23H59M59S'));
                $period = new DatePeriod($inicio, $interval, $fin); //frecuencia del intervalo a calcular
                foreach ($motivos as $motivo) {
                    $data = array();
                    foreach ($period as $date) {

                        $inicio = $date->format('Y-m-d H:i:s');
                        $categoria = $date->format('H:i');
                        $date->add(new DateInterval('PT59M59S'));
                        $fin = $date->format('Y-m-d H:i:s');
                        $report['xAxis']['categories'][] = $categoria;
                        array_push($data, $this->consulta($motivo, $inicio, $fin));
                    }

                    array_push($report['series'], array('name' => Util::Truncate($motivo->nombre, 21), 'data' => $data, 'type' => 'column')
                    );
                }
                break;
//dias
            case ($dias >= 1 && $dias <= 30):
                $fin->add(new DateInterval('PT23H59M59S'));
                $interval = DateInterval::createFromDateString('1 day');
                $period = new DatePeriod($inicio, $interval, $fin); //frecuencia del intervalo a calcular
                foreach ($motivos as $motivo) {
                    $data = array();
                    foreach ($period as $date) {
                        $inicio = $date->format('Y-m-d H:i:s');
                        $categoria = $date->format('d-M');
                        $date->add(new DateInterval('PT23H59M59S'));
                        $fin = $date->format('Y-m-d H:i:s');
                        $report['xAxis']['categories'][] = $categoria;
                        array_push($data, $this->consulta($motivo, $inicio, $fin));
                    }
                    array_push($report['series'], array('name' => Util::Truncate($motivo->nombre, 21), 'data' => $data, 'type' => 'column')
                    );
                }
                break;
//semanas
            case ($dias >= 31 && $dias <= 83):
                if ($inicio->format('D') != 'Mon') {
                    $inicio->modify('last monday');
                }
                if ($fin->format('D') == 'Mon') {
                    $fin->add(new DateInterval('P6DT23H59M59S'));
                }

                $interval = DateInterval::createFromDateString('1 week');
                $period = new DatePeriod($inicio, $interval, $fin); //frecuencia del intervalo a calcular
                foreach ($motivos as $motivo) {
                    $data = array();
                    foreach ($period as $date) {
                        $inicio = $date->format('Y-m-d H:i:s');
                        $categoria = $date->format('d/M');
                        $date->add(new DateInterval('P6DT23H59M59S'));
                        $fin = $date->format('Y-m-d H:i:s');
                        $categoria.=' - ' . $date->format('d/M');
                        $report['xAxis']['categories'][] = $categoria;
                        array_push($data, $this->consulta($motivo, $inicio, $fin));
                    }
                    array_push($report['series'], array('name' => Util::Truncate($motivo->nombre, 21), 'data' => $data, 'type' => 'column')
                    );
                }
                break;
//meses
            case ($dias >= 84):
                $interval = DateInterval::createFromDateString('1 month');
                $period = new DatePeriod($inicio, $interval, $fin); //frecuencia del intervalo a calcular
                foreach ($motivos as $motivo) {
                    $data = array();
                    foreach ($period as $date) {
                        $date->modify('first day of this month');
                        $inicio = $date->format('Y-m-d H:i:s');
                        $categoria = $date->format('F');
                        $date->modify('last day of this month');
                        $date->add(new DateInterval('PT23H59M59S'));
                        $fin = $date->format('Y-m-d H:i:s');
                        $report['xAxis']['categories'][] = $categoria;
                        array_push($data, $this->consulta($motivo, $inicio, $fin));
                    }
                    array_push($report['series'], array('name' => Util::Truncate($motivo->nombre, 21), 'data' => $data, 'type' => 'column')
                    );
                }
                break;
        }

        return $report;
    }

    private function consulta($motivo, $inicio, $fin) {
        $data = array();
        $consulta = Yii::app()->db->createCommand()
                ->select('ma.nombre, count(m.id) as count')
                ->from('mail m')
                ->leftJoin('mail_asunto ma', 'ma.id = m.asunto_id');
        if (empty($motivo->id)) {
            $consulta->where('m.asunto_id is null and m.estado=:estadom and m.fecha_envio between :inicio and :fin', array(':estadom' => 'ENVIADO', ':inicio' => $inicio, ':fin' => $fin));
        } else {
            $consulta->where('ma.estado=:estado and m.asunto_id = :motivo and m.estado=:estadom and m.fecha_envio between :inicio and :fin', array(':estado' => 'ACTIVO', ':estadom' => 'ENVIADO', ':motivo' => $motivo->id, ':inicio' => $inicio, ':fin' => $fin));
        }
        $consulta->group('ma.id')
                ->order('ma.nombre');

        $options = $consulta->queryAll();
        if (empty($motivo->id)) {
            
        }
        if (!empty($options)) {
            $data[] = (int) $options['0']['count'];
        } else {
            $data[] = (int) '0';
        }

        return $data;
    }

    public function mayorMenorMotivo($inicio, $fin, $tipo) {
        if ($tipo) {
            $tipo = 'desc';
        } else {
            $tipo = 'asc';
        }
        $inicio = DateTime::createFromFormat('d/m/Y', $inicio);
        $fin = DateTime::createFromFormat('d/m/Y', $fin)->modify('+1 day');

        $consulta = Yii::app()->db->createCommand()
                ->select('ma.nombre, count(m.id) as count')
                ->from('mail m')
                ->leftJoin('mail_asunto ma', 'ma.id = m.asunto_id')
                ->where('(ma.estado=:estado or ma.estado is null) and m.estado=:estadom and m.fecha_envio between :inicio and :fin', array(':estado' => 'ACTIVO', ':estadom' => 'ENVIADO', ':inicio' => $inicio->format('Y-m-d'), ':fin' => $fin->format('Y-m-d')))
                ->group('ma.id')
                ->order('count ' . $tipo)
                ->limit(1);
        $options = $consulta->queryAll();

        $consulta = Yii::app()->db->createCommand()
                ->select('count(m.id) as total')
                ->from('mail m')
                ->leftJoin('mail_asunto ma', 'ma.id = m.asunto_id')
                ->where('(ma.estado=:estado or ma.estado is null) and m.estado=:estadom and m.fecha_envio between :inicio and :fin', array(':estado' => 'ACTIVO', ':estadom' => 'ENVIADO', ':inicio' => $inicio->format('Y-m-d'), ':fin' => $fin->format('Y-m-d')));

        $total = $consulta->queryAll();
        if (!empty($options)) {
            $options['0']['count'] = round(($options['0']['count'] * 100) / $total['0']['total']);
            $options['0']['total'] = $total['0']['total'];
            $options['0']['nombre'] = $options['0']['nombre'] ? Util::Truncate($options['0']['nombre'], 35) : 'Otros';
        } else {
            $options['0'] = array();
        }
        return $options['0'];
    }

}
